package studio7.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import studio7.dao.BaseDAO;

import studio7.exception.DAOException;
import studio7.modelo.Instrumento;
import studio7.util.ConexionBD;

public class InstrumentoDAO2 extends BaseDAO{
	public Collection<Instrumento> buscarPorNombre(String nombreInstrumento) throws DAOException {
		System.out.println("InstrumentoDAO: buscarPorNombre(String nombreInstrumento)");
		String query = "SELECT * FROM Instrumentos";
		Collection<Instrumento> lista = new ArrayList<Instrumento>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, "%" + nombreInstrumento + "%");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Instrumento vo = new Instrumento();
	
				vo.setCodigoInstrumento(rs.getInt(0));
				vo.setNombreInstrumento(rs.getString("descripción"));
				vo.setMarcaInstrumento(rs.getString(2));
				vo.setTipoInstrumento(rs.getString("Caracteristicas Instrumento"));
				vo.setModeloInstrumento(rs.getString(4));
				vo.setCaracteristicaInstrumento(rs.getString(5));
				//vo.setLocal(rs.getInt(5));
				
					
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return lista;
	}

	
public void insertar(Instrumento ins) throws DAOException {
		System.out.println("InstrumentoDAO: insertar(Instrumento ins)");
		String query = "INSERT INTO instrumento(idInstrumento, nombre, marca, tipo, modelo, caracteristica) VALUES (?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			
			stmt.setInt(1, ins.getCodigoInstrumento());
			stmt.setString(2, ins.getNombreInstrumento());
			stmt.setString(3, ins.getMarcaInstrumento());
			stmt.setString(4, ins.getTipoInstrumento());
			stmt.setString(5, ins.getModeloInstrumento());
			stmt.setString(6, ins.getCaracteristicaInstrumento());
			//stmt.setString(7, ins.getSalaIdSala());
			//stmt.setInt(7, ins.getSalaIdSala());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public Instrumento obtener(String rolNombre) throws DAOException {
		System.out.println("InstrumentoDAO: obtener(String nombre)");
		Instrumento vo = new Instrumento();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select codigoInstrumento, nombreInstrumento, marcaInstrumento, tipoInstrumento,modeloInstrumento, caracteristicaInstrumento from Instrumento where nombreInstrumento=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, rolNombre);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setCodigoInstrumento(rs.getInt(0));
				vo.setNombreInstrumento(rs.getString(1));
				vo.setMarcaInstrumento(rs.getString(2));
				vo.setTipoInstrumento(rs.getString(3));
				vo.setModeloInstrumento(rs.getString(4));
				vo.setCaracteristicaInstrumento(rs.getString(5));
			    //vo.setLocal(rs.getInt(6));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public void eliminar(String nombre) throws DAOException {
		System.out.println("InstrumentoDAO: eliminar(String Nombre)");
		String query = "DELETE FROM Instrumento WHERE nombre=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(2,nombre);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public void actualizar(Instrumento vo) throws DAOException {
		System.out.println("RolDAO: actualizar(Instrumento vo)");
		String query = "update Instrumento set descripcion=? where nombre=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(0, vo.getCodigoInstrumento());
			stmt.setString(1, vo.getNombreInstrumento());
			stmt.setString(2, vo.getMarcaInstrumento());
			stmt.setString(3, vo.getTipoInstrumento());
			stmt.setString(4,vo.getModeloInstrumento());
			stmt.setString(4,vo.getCaracteristicaInstrumento());
			//stmt.setInt(5,vo.getLocal());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	
public Collection<Instrumento> listar() throws DAOException {
		System.out.println("InstrumentoDAO: listar()");
		Collection<Instrumento> c = new ArrayList<Instrumento>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "SELECT codigoInstrumento, nombreInstrumento, marcaInstrumento, tipoInstrumento,modeloInstrumento, caracteristicaInstrumento from Instrumento";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Instrumento vo = new Instrumento();
				
				vo.setCodigoInstrumento(rs.getInt(0));
				vo.setNombreInstrumento(rs.getString(1));
				vo.setMarcaInstrumento(rs.getString(2));
				vo.setTipoInstrumento(rs.getString(3));
				vo.setModeloInstrumento(rs.getString(4));
				vo.setCaracteristicaInstrumento(rs.getString(5));
			    //vo.setLocal(rs.getInt(6));
				
				c.add(vo);
				
			
				
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOException(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}

public String  BuscarInstrumento(int idInstrumento) throws DAOException {
	Connection con = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	String nombreInstrumento="";
	try {
		con = ConexionBD.obtenerConexion();
		String query = "SELECT nombre  from instrumento where idInstrumento=?";
		stmt = con.prepareStatement(query);
		stmt.setInt(1,idInstrumento);
		rs = stmt.executeQuery();
		
		while (rs.next()) {
			nombreInstrumento=rs.getString(1);
			
		}
	} catch (SQLException e) {
		System.err.println(e.getMessage());
		throw new DAOException(e.getMessage());
	} finally {
		this.cerrarStatement(stmt);
		this.cerrarConexion(con);
	}
	return nombreInstrumento;
}
}
